// HotPostMapper.java
package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.Post;
import org.apache.ibatis.annotations.*;
import java.util.Date;
import java.util.List;

@Mapper
public interface HotPostMapper {
    @Select("SELECT * FROM posts WHERE post_id = #{postId}")
    Post getPostById(@Param("postId") Long postId);

    @Select("SELECT view_count FROM posts WHERE post_id = #{postId}")
    int getViewCountByPostId(@Param("postId") Long postId);

    @Select("SELECT COUNT(*) FROM post_comments WHERE post_id = #{postId} AND user_id = #{userId} AND status = 1")
    int getCommentCountByPostIdAndUser(@Param("postId") Long postId, @Param("userId") Long userId);

    @Select("SELECT COUNT(*) FROM post_likes WHERE post_id = #{postId} AND user_id = #{userId}")
    int getLikeCountByPostIdAndUser(@Param("postId") Long postId, @Param("userId") Long userId);

    @Select("SELECT COUNT(*) FROM post_favorites WHERE post_id = #{postId} AND user_id = #{userId}")
    int getFavoriteCountByPostIdAndUser(@Param("postId") Long postId, @Param("userId") Long userId);

    @Select("SELECT COUNT(*) FROM posts WHERE status = 1")
    int getTotalHotPostsCount();

    @Select("SELECT COUNT(*) FROM posts WHERE status = 1 AND created_at BETWEEN #{startDate} AND #{endDate}")
    int getHotPostsCountByDateRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("<script>" +
            "SELECT COUNT(*) FROM posts WHERE status = 1 " +
            "<when test='timeRange != null'>" +
            "   <choose>" +
            "       <when test='timeRange == \"week\"'>" +
            "           AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" +
            "       </when>" +
            "       <when test='timeRange == \"month\"'>" +
            "           AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)" +
            "       </when>" +
            "   </choose>" +
            "</when>" +
            "</script>")
    int getHotPostsCountByTimeRange(@Param("timeRange") String timeRange);

    @Select("<script>" +
            "SELECT p.* FROM posts p WHERE p.status = 1 " +
            "<when test='timeRange != null'>" +
            "   <choose>" +
            "       <when test='timeRange == \"week\"'>" +
            "           AND p.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" +
            "       </when>" +
            "       <when test='timeRange == \"month\"'>" +
            "           AND p.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)" +
            "       </when>" +
            "   </choose>" +
            "</when>" +
            "ORDER BY " +
            "(p.view_count*0.3 + " +
            "(SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.post_id AND pc.status = 1)*0.25 + " +
            "(SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.post_id)*0.25 + " +
            "(SELECT COUNT(*) FROM post_favorites pf WHERE pf.post_id = p.post_id)*0.2) * " +
            "GREATEST(0, 1 - DATEDIFF(NOW(), p.created_at) * 0.01) " + // 添加时间衰减因子
            "DESC LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Post> getHotPostsByTimeRange(@Param("timeRange") String timeRange,
                                      @Param("offset") int offset,
                                      @Param("pageSize") int pageSize);

    @Select("SELECT p.* FROM posts p WHERE p.status = 1 AND p.created_at BETWEEN #{startDate} AND #{endDate} " +
            "ORDER BY " +
            "(p.view_count*0.3 + " +
            "(SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.post_id AND pc.status = 1)*0.25 + " +
            "(SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.post_id)*0.25 + " +
            "(SELECT COUNT(*) FROM post_favorites pf WHERE pf.post_id = p.post_id)*0.2) " +
            "DESC LIMIT #{offset}, #{pageSize}")
    List<Post> getHotPostsByDateRangeWithPagination(@Param("startDate") Date startDate,
                                                    @Param("endDate") Date endDate,
                                                    @Param("offset") int offset,
                                                    @Param("pageSize") int pageSize);

    @Select("SELECT p.* FROM posts p WHERE p.status = 1 " +
            "ORDER BY " +
            "(p.view_count*0.3 + " +
            "(SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.post_id AND pc.status = 1)*0.25 + " +
            "(SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.post_id)*0.25 + " +
            "(SELECT COUNT(*) FROM post_favorites pf WHERE pf.post_id = p.post_id)*0.2) " +
            "DESC LIMIT #{offset}, #{pageSize}")
    List<Post> getHotPostsWithPagination(@Param("offset") int offset, @Param("pageSize") int pageSize);
}